IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vStudentClassesAttended]'))
DROP VIEW [dbo].[vStudentClassesAttended]
go
create view [dbo].[vStudentClassesAttended]
as 
SELECT     cm.StudentId, cm.CandidateCode, cm.Grade, cm.Attend, p.PeriodDate, p.PeriodCode, p.PeriodName, c.PassGrade, c.MaxGrade, ls.LessonCode, ls.LessonName, 
                      cm.ClassMemberId, c.ClassId, c.PeriodId, s.FirstName, s.LastName, s.Company, s.IdNumber, ls.LessonId, p.isOpen, 
                      CASE WHEN p.isOpen = 1 THEN 'Open' ELSE 'Closed' END AS PeriodStatus, dst.DiplomaSuperTypeCode, dst.DiplomaSuperTypeId, dst.DiplomaSuperTypeName, 
                      c.ExamDate
FROM         ClassMember AS cm INNER JOIN
                      Class AS c ON cm.ClassId = c.ClassId INNER JOIN
                      Period AS p ON c.PeriodId = p.PeriodId INNER JOIN
                      Lesson AS ls ON c.LessonId = ls.LessonId INNER JOIN
                      Student AS s ON cm.StudentId = s.StudentId INNER JOIN
                      DiplomaSuperType AS dst ON p.DiplomaSuperTypeId = dst.DiplomaSuperTypeId

go
